{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Amazon Redshift - Setup IAM Access to S3 and Athena\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import boto3\n",
    "from botocore.exceptions import ClientError\n",
    "\n",
    "iam = boto3.client('iam')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create AssumeRolePolicyDocument"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "assume_role_policy_doc = {\n",
    "  \"Version\": \"2012-10-17\",\n",
    "  \"Statement\": [\n",
    "    {\n",
    "      \"Effect\": \"Allow\",\n",
    "      \"Principal\": {\n",
    "        \"Service\": \"redshift.amazonaws.com\"\n",
    "      },\n",
    "      \"Action\": \"sts:AssumeRole\"\n",
    "    }\n",
    "  ]\n",
    "} "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create Role"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "iam_redshift_role_name = 'DSOAWS_Redshift'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    iam_role_redshift = iam.create_role(\n",
    "        RoleName=iam_redshift_role_name,\n",
    "        AssumeRolePolicyDocument=json.dumps(assume_role_policy_doc),\n",
    "        Description='DSOAWS Redshift Role'\n",
    "    )\n",
    "except ClientError as e:\n",
    "    if e.response['Error']['Code'] == 'EntityAlreadyExists':\n",
    "        print(\"Role already exists\")\n",
    "    else:\n",
    "        print(\"Unexpected error: %s\" % e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "iam_role_redshift_arn = iam_role_redshift['Role']['Arn']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(iam_role_redshift_arn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### This is how you get the Role ARN from other notebooks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Call the role from other noteboosk\n",
    "role = iam.get_role(RoleName='DSOAWS_Redshift')\n",
    "role_arn = role['Role']['Arn']\n",
    "print(role_arn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create Self-Managed Policies"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Define Policies"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### arn:aws:iam::aws:policy/AmazonS3FullAccess"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_redshift_to_s3 = {\n",
    "    \"Version\": \"2012-10-17\",\n",
    "    \"Statement\": [\n",
    "        {\n",
    "            \"Effect\": \"Allow\",\n",
    "            \"Action\": \"s3:*\",\n",
    "            \"Resource\": \"*\"\n",
    "        }\n",
    "    ]\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### arn:aws:iam::aws:policy/AmazonAthenaFullAccess"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_redshift_to_athena = {\n",
    "    \"Version\": \"2012-10-17\",\n",
    "    \"Statement\": [\n",
    "        {\n",
    "            \"Effect\": \"Allow\",\n",
    "            \"Action\": [\n",
    "                \"athena:*\"\n",
    "            ],\n",
    "            \"Resource\": [\n",
    "                \"*\"\n",
    "            ]\n",
    "        },\n",
    "        {\n",
    "            \"Effect\": \"Allow\",\n",
    "            \"Action\": [\n",
    "                \"glue:CreateDatabase\",\n",
    "                \"glue:DeleteDatabase\",\n",
    "                \"glue:GetDatabase\",\n",
    "                \"glue:GetDatabases\",\n",
    "                \"glue:UpdateDatabase\",\n",
    "                \"glue:CreateTable\",\n",
    "                \"glue:DeleteTable\",\n",
    "                \"glue:BatchDeleteTable\",\n",
    "                \"glue:UpdateTable\",\n",
    "                \"glue:GetTable\",\n",
    "                \"glue:GetTables\",\n",
    "                \"glue:BatchCreatePartition\",\n",
    "                \"glue:CreatePartition\",\n",
    "                \"glue:DeletePartition\",\n",
    "                \"glue:BatchDeletePartition\",\n",
    "                \"glue:UpdatePartition\",\n",
    "                \"glue:GetPartition\",\n",
    "                \"glue:GetPartitions\",\n",
    "                \"glue:BatchGetPartition\"\n",
    "            ],\n",
    "            \"Resource\": [\n",
    "                \"*\"\n",
    "            ]\n",
    "        },\n",
    "        {\n",
    "            \"Effect\": \"Allow\",\n",
    "            \"Action\": [\n",
    "                \"s3:GetBucketLocation\",\n",
    "                \"s3:GetObject\",\n",
    "                \"s3:ListBucket\",\n",
    "                \"s3:ListBucketMultipartUploads\",\n",
    "                \"s3:ListMultipartUploadParts\",\n",
    "                \"s3:AbortMultipartUpload\",\n",
    "                \"s3:CreateBucket\",\n",
    "                \"s3:PutObject\"\n",
    "            ],\n",
    "            \"Resource\": [\n",
    "                \"arn:aws:s3:::aws-athena-query-results-*\"\n",
    "            ]\n",
    "        },\n",
    "        {\n",
    "            \"Effect\": \"Allow\",\n",
    "            \"Action\": [\n",
    "                \"s3:GetObject\",\n",
    "                \"s3:ListBucket\"\n",
    "            ],\n",
    "            \"Resource\": [\n",
    "                \"arn:aws:s3:::athena-examples*\"\n",
    "            ]\n",
    "        },\n",
    "        {\n",
    "            \"Effect\": \"Allow\",\n",
    "            \"Action\": [\n",
    "                \"s3:ListBucket\",\n",
    "                \"s3:GetBucketLocation\",\n",
    "                \"s3:ListAllMyBuckets\"\n",
    "            ],\n",
    "            \"Resource\": [\n",
    "                \"*\"\n",
    "            ]\n",
    "        },\n",
    "        {\n",
    "            \"Effect\": \"Allow\",\n",
    "            \"Action\": [\n",
    "                \"sns:ListTopics\",\n",
    "                \"sns:GetTopicAttributes\"\n",
    "            ],\n",
    "            \"Resource\": [\n",
    "                \"*\"\n",
    "            ]\n",
    "        },\n",
    "        {\n",
    "            \"Effect\": \"Allow\",\n",
    "            \"Action\": [\n",
    "                \"cloudwatch:PutMetricAlarm\",\n",
    "                \"cloudwatch:DescribeAlarms\",\n",
    "                \"cloudwatch:DeleteAlarms\"\n",
    "            ],\n",
    "            \"Resource\": [\n",
    "                \"*\"\n",
    "            ]\n",
    "        },\n",
    "        {\n",
    "            \"Effect\": \"Allow\",\n",
    "            \"Action\": [\n",
    "                \"lakeformation:GetDataAccess\"\n",
    "            ],\n",
    "            \"Resource\": [\n",
    "                \"*\"\n",
    "            ]\n",
    "        }\n",
    "    ]\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create Policy Objects"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    policy_redshift_s3 = iam.create_policy(\n",
    "      PolicyName='DSOAWS_RedshiftPolicyToS3',\n",
    "      PolicyDocument=json.dumps(my_redshift_to_s3)\n",
    "    )\n",
    "except ClientError as e:\n",
    "    if e.response['Error']['Code'] == 'EntityAlreadyExists':\n",
    "        print(\"Policy already exists\")\n",
    "    else:\n",
    "        print(\"Unexpected error: %s\" % e)\n",
    "        \n",
    "print(policy_redshift_s3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get ARN\n",
    "policy_redshift_s3_arn = policy_redshift_s3['Policy']['Arn']\n",
    "print(policy_redshift_s3_arn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    policy_redshift_athena = iam.create_policy(\n",
    "      PolicyName='DSOAWS_RedshiftPolicyToAthena',\n",
    "      PolicyDocument=json.dumps(my_redshift_to_athena)\n",
    "    )\n",
    "except ClientError as e:\n",
    "    if e.response['Error']['Code'] == 'EntityAlreadyExists':\n",
    "        print(\"Policy already exists\")\n",
    "    else:\n",
    "        print(\"Unexpected error: %s\" % e)\n",
    "        \n",
    "print(policy_redshift_athena)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get ARN\n",
    "policy_redshift_athena_arn = policy_redshift_athena['Policy']['Arn']\n",
    "print(policy_redshift_athena_arn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Attach Policies To Role"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Attach DSOAWS_RedshiftPolicyToAthena policy\n",
    "try:\n",
    "    response = iam.attach_role_policy(\n",
    "        PolicyArn=policy_redshift_athena_arn,\n",
    "        RoleName=iam_redshift_role_name\n",
    "    )\n",
    "except ClientError as e:\n",
    "    if e.response['Error']['Code'] == 'EntityAlreadyExists':\n",
    "        print(\"Policy is already attached\")\n",
    "    else:\n",
    "        print(\"Unexpected error: %s\" % e)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Attach DSOAWS_RedshiftPolicyToS3 policy\n",
    "try:\n",
    "    response = iam.attach_role_policy(\n",
    "        PolicyArn=policy_redshift_s3_arn,\n",
    "        RoleName=iam_redshift_role_name\n",
    "    )\n",
    "except ClientError as e:\n",
    "    if e.response['Error']['Code'] == 'EntityAlreadyExists':\n",
    "        print(\"Policy is already attached\")\n",
    "    else:\n",
    "        print(\"Unexpected error: %s\" % e)\n",
    "        "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "See: https://github.com/awslabs/amazon-sagemaker-examples/tree/master/advanced_functionality/working_with_redshift_data for more examples"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "conda_python3",
   "language": "python",
   "name": "conda_python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
